package com.sqlcheck.service.impl;

import com.sqlcheck.dto.request.TestDatabaseConnectionRequest;
import com.sqlcheck.dto.response.TestDatabaseConnectionResponse;
import com.sqlcheck.service.DatabaseConnectionTestService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.Properties;

@Slf4j
@Service
public class DatabaseConnectionTestServiceImpl implements DatabaseConnectionTestService {

    @Override
    public TestDatabaseConnectionResponse testConnection(TestDatabaseConnectionRequest request) {
        long startTime = System.currentTimeMillis();

        try {
            String jdbcUrl = buildJdbcUrl(request);
            Properties props = new Properties();
            props.setProperty("user", request.getUsername());
            props.setProperty("password", request.getPassword());

            // 设置连接超时
            props.setProperty("connectTimeout", "10000"); // 10秒超时
            props.setProperty("socketTimeout", "10000");

            log.info("尝试连接数据库: {}", jdbcUrl);

            try (Connection connection = DriverManager.getConnection(jdbcUrl, props)) {
                // 测试连接是否有效
                if (connection.isValid(5)) {
                    String databaseVersion = getDatabaseVersion(connection, request.getDbType());
                    long connectionTime = System.currentTimeMillis() - startTime;

                    log.info("数据库连接成功，版本: {}, 耗时: {}ms", databaseVersion, connectionTime);
                    return TestDatabaseConnectionResponse.success(
                            "数据库连接成功",
                            databaseVersion,
                            connectionTime);
                } else {
                    return TestDatabaseConnectionResponse.failure("数据库连接无效");
                }
            }

        } catch (SQLException e) {
            String errorMsg = e.getMessage();
            log.error("数据库连接失败: {}", errorMsg);

            // 提供更具体的错误提示
            if (errorMsg.contains("Bad packet type") || errorMsg.contains("protocol")) {
                return TestDatabaseConnectionResponse.failure("连接失败: 数据库类型与端口不匹配。" +
                        "如果是MySQL数据库，请选择MySQL类型并使用3306端口；" +
                        "如果是Oracle数据库，请选择Oracle类型并使用1521端口。原始错误: " + errorMsg);
            } else if (errorMsg.contains("Connection refused") || errorMsg.contains("timeout")) {
                return TestDatabaseConnectionResponse.failure("连接失败: 无法连接到数据库服务器，请检查主机地址和端口是否正确。原始错误: " + errorMsg);
            } else if (errorMsg.contains("Access denied") || errorMsg.contains("authentication")) {
                return TestDatabaseConnectionResponse.failure("连接失败: 用户名或密码错误。原始错误: " + errorMsg);
            } else {
                return TestDatabaseConnectionResponse.failure("连接失败: " + errorMsg);
            }
        } catch (Exception e) {
            log.error("测试数据库连接时发生异常: {}", e.getMessage());
            return TestDatabaseConnectionResponse.failure("连接测试异常: " + e.getMessage());
        }
    }

    /**
     * 构建JDBC连接URL
     */
    private String buildJdbcUrl(TestDatabaseConnectionRequest request) {
        String dbType = request.getDbType().toUpperCase();

        // 智能检测：如果是Oracle类型但使用了MySQL端口，给出警告并建议
        if ("ORACLE".equals(dbType) && request.getPort() == 3306) {
            log.warn("检测到Oracle类型配置但使用了MySQL默认端口3306，这可能导致连接失败。Oracle默认端口通常是1521");
        }
        if ("MYSQL".equals(dbType) && request.getPort() == 1521) {
            log.warn("检测到MySQL类型配置但使用了Oracle默认端口1521，这可能导致连接失败。MySQL默认端口通常是3306");
        }

        switch (dbType) {
            case "MYSQL":
                String mysqlUrl = String.format("jdbc:mysql://%s:%d/%s",
                        request.getHost(), request.getPort(), request.getDatabase());
                mysqlUrl += "?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
                if (request.getSchema() != null && !request.getSchema().trim().isEmpty()) {
                    mysqlUrl += "&useInformationSchema=true";
                }
                return mysqlUrl;

            case "ORACLE":
                // Oracle 支持两种连接方式：
                // 1. SERVICE_NAME (推荐): jdbc:oracle:thin:@//host:port/service_name
                // 2. SID (旧版): jdbc:oracle:thin:@host:port:sid
                String connectionType = request.getOracleConnectionType();
                String dbIdentifier = request.getDatabase();
                String oracleUrl;

                if ("SID".equalsIgnoreCase(connectionType)) {
                    // 使用 SID 方式
                    oracleUrl = String.format("jdbc:oracle:thin:@%s:%d:%s",
                            request.getHost(), request.getPort(), dbIdentifier);
                    log.info("使用 Oracle SID 连接方式: {}", dbIdentifier);
                } else {
                    // 默认使用 SERVICE_NAME 方式
                    oracleUrl = String.format("jdbc:oracle:thin:@//%s:%d/%s",
                            request.getHost(), request.getPort(), dbIdentifier);
                    log.info("使用 Oracle SERVICE_NAME 连接方式: {}", dbIdentifier);
                }
                return oracleUrl;

            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + dbType);
        }
    }

    /**
     * 获取数据库版本信息
     */
    private String getDatabaseVersion(Connection connection, String dbType) {
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            String productName = metaData.getDatabaseProductName();
            String productVersion = metaData.getDatabaseProductVersion();

            // 根据数据库类型格式化版本信息
            String dbTypeUpper = dbType.toUpperCase();
            switch (dbTypeUpper) {
                case "MYSQL":
                    return formatMySQLVersion(productName, productVersion);
                case "ORACLE":
                    return formatOracleVersion(productName, productVersion, connection);
                default:
                    return productName + " " + productVersion;
            }
        } catch (SQLException e) {
            log.warn("获取数据库版本信息失败: {}", e.getMessage());
            return "Unknown Version";
        }
    }

    /**
     * 格式化MySQL版本信息
     */
    private String formatMySQLVersion(String productName, String productVersion) {
        try {
            // MySQL版本格式通常为: 8.0.33 或 5.7.44-log
            // 提取主要版本号
            String cleanVersion = productVersion;
            if (cleanVersion.contains("-")) {
                cleanVersion = cleanVersion.substring(0, cleanVersion.indexOf("-"));
            }

            return "MySQL " + cleanVersion;
        } catch (Exception e) {
            log.warn("格式化MySQL版本失败: {}", e.getMessage());
            return productName + " " + productVersion;
        }
    }

    /**
     * 格式化Oracle版本信息
     */
    private String formatOracleVersion(String productName, String productVersion, Connection connection) {
        try {
            // 尝试获取更详细的Oracle版本信息
            try (Statement stmt = connection.createStatement();
                    ResultSet rs = stmt
                            .executeQuery("SELECT BANNER FROM V$VERSION WHERE BANNER LIKE 'Oracle Database%'")) {

                if (rs.next()) {
                    String banner = rs.getString(1);
                    // 从banner中提取版本信息，格式如: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
                    // - Production
                    if (banner.contains("Release")) {
                        String releaseInfo = banner.substring(banner.indexOf("Release") + 8);
                        if (releaseInfo.contains(" ")) {
                            releaseInfo = releaseInfo.substring(0, releaseInfo.indexOf(" "));
                        }

                        // 确定Oracle版本系列
                        if (banner.contains("19c")) {
                            return "Oracle 19c (" + releaseInfo + ")";
                        } else if (banner.contains("18c")) {
                            return "Oracle 18c (" + releaseInfo + ")";
                        } else if (banner.contains("12c")) {
                            return "Oracle 12c (" + releaseInfo + ")";
                        } else if (banner.contains("11g")) {
                            return "Oracle 11g (" + releaseInfo + ")";
                        } else {
                            return "Oracle " + releaseInfo;
                        }
                    }
                }
            } catch (SQLException e) {
                log.debug("无法查询V$VERSION表，使用基本版本信息: {}", e.getMessage());
            }

            // 回退到基本版本信息
            return productName + " " + productVersion;

        } catch (Exception e) {
            log.warn("格式化Oracle版本失败: {}", e.getMessage());
            return productName + " " + productVersion;
        }
    }
}